package com.caiheng.api.util.sql;

import cn.hutool.core.util.StrUtil;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @创建者：zhouwei
 * @创建时间：2022/5/13
 * @描述：
 */
@Component
public final class SqlFileExecutor {

//    @Autowired
//    private CreateTableUtil createTableUtil;

    public static void main(String[] args) {
        try {
//            List<String> sqlList = loadSql("src/test/resources/config/script.sql");
//            System.out.println("size:" + sqlList.size());
//            for (String sql : sqlList) {
//                System.out.println(sql);
//            }
//            String sql = loadSqlToStr("D:\\Project\\Java\\yuedou\\charger_order.sql","testName");
//            System.out.println("sql: " + sql);

            CreateTableUtil.executeSQL("D:\\Project\\Java\\yuedou\\charger_order.sql","testName");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 读取 SQL 文件，获取 SQL 语句
     * @param sqlFile
     * SQL 脚本文件
     * @return List 返回所有 SQL 语句的 List
     * @throws Exception
     */
    public static List<String> loadSql(String sqlFile,String tableName) throws Exception {
        List<String> sqlList = new ArrayList<>();
        try {
            InputStream sqlFileIn = new FileInputStream(sqlFile);
            StringBuffer sqlSb = new StringBuffer();
            byte[] buff = new byte[1024];
            int byteRead = 0;
            while ((byteRead = sqlFileIn.read(buff)) != -1) {
                sqlSb.append(new String(buff, 0, byteRead));
            }
            // Windows 下换行是 \\r\\n, Linux 下是 \\n
            String[] sqlArr = sqlSb.toString() .split("(;\\\\s*\\\\r\\\\n)|(;\\\\s*\\\\n)");
            for (int i = 0; i < sqlArr.length; i++) {
                String sql = sqlArr[i].replaceAll("--.*", "").trim();

                if(StrUtil.isNotEmpty(tableName)){
                    if(sql.contains("charger_order")){
                        sql = sql.replace("charger_order",tableName);
                    }
                }

                if (!sql.equals("")) {
                    sqlList.add(sql);
                }
            }
            return sqlList;
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
    }


    /**
     *  读取sql文件 成 文本字符串
     * @param sqlFile
     * @return
     */
    public static String loadSqlToStr(String sqlFile,String tableName){
        String str = "";
        try {
            List<String> sqlList = loadSql(sqlFile,tableName);
            for (String sql : sqlList) {
                str += sql;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return str;
    }


    /**
     * 传入连接来执行 SQL 脚本文件，这样可与其外的数据库操作同处一个事物中
     *
     * @param conn
     * 传入数据库连接
     * @param sqlFile
     * SQL 脚本文件 可选参数，为空字符串或为null时 默认路径为 src/test/resources/config/script.sql
     * @throws Exception
     */
    public static void execute(Connection conn, String sqlFile) throws Exception {
        Statement stmt = null;
        if(sqlFile == null || "".equals(sqlFile)){
            sqlFile="src/test/resources/config/script.sql";
        }
        List<String> sqlList = loadSql(sqlFile,null);
        stmt = conn.createStatement();
        for (String sql : sqlList) {
            stmt.addBatch(sql);
        }
        int[] rows = stmt.executeBatch();
        System.out.println("Row count:" + Arrays.toString(rows));
    }

}
